package com.o2o.cleaning.month.platform.ebusiness_plat.jumei_2019_7.jumei_lyc

import org.apache.spark.sql.SparkSession

object jumei_2019_07_is_onsell_false {
  def main(args:Array[String]): Unit = {
    val spark = SparkSession.builder().appName("jumei_count").master("local[*]")
      .config("fs.s3a.access.key", "GAO7EO9FWKPJ8WFCQDME")
      .config("fs.s3a.secret.key", "LZ0xaHBSYKHaJ9ECDbX9f7zin79UZkXfGoNapRPL")
      .config("fs.s3a.endpoint", "https://obs.cn-north-1.myhuaweicloud.com")
      .getOrCreate()
    val sc = spark.sparkContext
    val sqlContext = spark.sqlContext

    sqlContext.read.orc("s3a://o2o-dataproces-group/li_yinchao/lyc/platform/jumei/2019/7/jumei_2019_7_2/brand_join/")
        .select("good_id","sellCount","salesAmount")
        .where("sellCount > 0 and salesAmount > 0")
        .registerTempTable("table")

    sqlContext.sql(
      """
        |select
        |count(good_id) count_id,
        |sum(sellCount) sellCount,
        |sum(salesAmount) salesAmount
        |from table
      """.stripMargin)
        .show()











//    sqlContext.read.json("s3a://o2o-sourcedata/obs-source-2019/7/Jumei/jumei_app_sellCount/")
//      .select("good_id","add_to_field","is_onsell")
//      .registerTempTable("SourceData")
//
//    val aa =  sqlContext.read.json("s3a://o2o-dataproces-group/liu_taowei/month_data/plat_need/jumei/is_onsell_false/*")
//    aa.registerTempTable("lastData")
//
//    //    关联不到的下架的相乘
//    sqlContext.sql(
//      """
//        |select
//        |a.*
//        |from SourceData a
//        |left join
//        |lastData b
//        |on a.good_id = b.good_id
//        |where b.good_id is null and a.is_onsell = 'false'
//      """.stripMargin)
//        .repartition(1).write
//      .json("s3a://o2o-dataproces-group/li_yinchao/lyc/platform/jumei/2019/7/jumei_2019_7/is_onsell_false/")



    sc.stop()
  }
}
